/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;


import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import model.UserBean;
import validation.LoginValidation;

/**
 *
 * @author Jagadeesh
 */
public class DBClass {
String idGudangAdmin;
    public static Connection createConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/rbpl2", "root", "");
        return connection;
    }

    //we get values from servlet by passing bean object to insertdetails method
    public int insertDetails(UserBean bb) throws SQLException, ClassNotFoundException {
        try {
            Connection con = createConnection();
            PreparedStatement pstmt = con.prepareStatement("insert into userdetails (fullName, userName, password, dateOfBirth, email, phoneNo, prov_id,kab_id,kec_id,desa_id) values(?,?,md5(?),?,?,?,?,?,?,?)");
            //set values to prepared statement object by getting values from bean object
            pstmt.setString(1, bb.getfullName());
            pstmt.setString(2, bb.getUserName());
            pstmt.setString(3, bb.getPassword()+"rbplsalting#12345");
            pstmt.setString(4, bb.getDateOfBirth());
            pstmt.setString(5, bb.getEmail());
            pstmt.setString(6, bb.getPhoneNo());
            pstmt.setString(7, bb.getProvinsi());
            pstmt.setString(8, bb.getKabupaten());
            pstmt.setString(9, bb.getKecamatan());
            pstmt.setString(10, bb.getDesa());
            int i = pstmt.executeUpdate();
            System.out.println(pstmt);
            return i;
        } catch (Exception e) {
            System.out.println(e);
            return 0;
        }
    }

    public List getAlldetails() throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select fullName, userName, password, dateOfBirth, email, phoneNo, prov_id,kab_id,kec_id,desa_id,idGudangAdmin from userdetails");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            UserBean ubean = new UserBean();
            ubean.setfullName(rs.getString("fullName"));
            ubean.setUserName(rs.getString("userName"));
            ubean.setPassword(rs.getString("password"));
            ubean.setDateOfBirth(rs.getString("dateOfBirth"));
            ubean.setEmail(rs.getString("email"));
            ubean.setPhoneNo(rs.getString("phoneNo"));
            ubean.setProvinsi(rs.getString("prov_id"));
            ubean.setKabupaten(rs.getString("kab_id"));
            ubean.setKecamatan(rs.getString("kec_id"));
            ubean.setDesa(rs.getString("desa_id"));
            ubean.setIdGudangAdmin(rs.getString("idGudangAdmin"));
            list.add(ubean);
         
            
        }
        return list;
    }

 public List getResult(String nilai) throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select DISTINCT fullName, userName, password, dateOfBirth, email, phoneNo, prov_id,kab_id,kec_id,desa_id from userdetails,desa where userdetails.desa_id = desa.id and desa.nama='"+nilai+"'" );
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            UserBean ubean = new UserBean();
            ubean.setfullName(rs.getString("fullName"));
            ubean.setUserName(rs.getString("userName"));
            ubean.setPassword(rs.getString("password"));
            ubean.setDateOfBirth(rs.getString("dateOfBirth"));
            ubean.setEmail(rs.getString("email"));
            ubean.setPhoneNo(rs.getString("phoneNo"));
            ubean.setProvinsi(rs.getString("prov_id"));
            ubean.setKabupaten(rs.getString("kab_id"));
            ubean.setKecamatan(rs.getString("kec_id"));
            ubean.setDesa(rs.getString("desa_id"));
            
            list.add(ubean);

        }
        return list;
    }
 
 
    public UserBean getDetails(String uname) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select fullName, userName, password, dateOfBirth, email, phoneNo, prov_id,kab_id,kec_id,desa_id from userdetails where userName=?");
        pstmt.setString(1, uname);
        ResultSet rs = pstmt.executeQuery();

        UserBean ubean = new UserBean();
        while (rs.next()) {

            ubean.setfullName(rs.getString("fullName"));
            ubean.setUserName(rs.getString("userName"));
            ubean.setPassword(rs.getString("password"));
            ubean.setDateOfBirth(rs.getString("dateOfBirth"));
            ubean.setEmail(rs.getString("email"));
            ubean.setPhoneNo(rs.getString("phoneNo"));
            ubean.setProvinsi(rs.getString("prov_id"));
            ubean.setKabupaten(rs.getString("kab_id"));
            ubean.setKecamatan(rs.getString("kec_id"));
            ubean.setDesa(rs.getString("desa_id"));
            
        }
        ubean.setAction("update");
        return ubean;
    }

       
    public List getKabupaten(String uname) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from kabupaten where id_prov=?");
        pstmt.setString(1, uname);
        ResultSet rs = pstmt.executeQuery();

        List list = new ArrayList();
        while (rs.next()) {
            UserBean ubean = new UserBean();
            ubean.setIdTabel(rs.getString(1));
            ubean.setNamaTabel(rs.getString(3));

            list.add(ubean);

        }
        return list;
    }

    public List getKecamatan(String uname) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from kecamatan where id_kabkota=?");
        pstmt.setString(1, uname);
        ResultSet rs = pstmt.executeQuery();

        List list = new ArrayList();
        while (rs.next()) {
            UserBean ubean = new UserBean();
            ubean.setIdTabel(rs.getString(1));
            ubean.setNamaTabel(rs.getString(3));

            list.add(ubean);

        }
        return list;
    }

    public List getDesa(String uname) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from desa where id_kecamatan=?");
        pstmt.setString(1, uname);
        ResultSet rs = pstmt.executeQuery();

        List list = new ArrayList();
        while (rs.next()) {
            UserBean ubean = new UserBean();
            ubean.setIdTabel(rs.getString(1));
            ubean.setNamaTabel(rs.getString(3));

            list.add(ubean);

        }
        return list;
    }
    public List getJenis() throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from jenis");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            UserBean ubean = new UserBean();
            ubean.setIdJenis(rs.getString(1));
            ubean.setNamaJenis(rs.getString(2));

            list.add(ubean);

        }
        return list;
    }
    
 public List getGudang() throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from gudang");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            UserBean ubean = new UserBean();
            ubean.setIdGgudang(rs.getString(1));
            ubean.setNamaGudang(rs.getString(2));

            list.add(ubean);

        }
        return list;
    }
  public List getStatus() throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from statuss");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            UserBean ubean = new UserBean();
            ubean.setIdStatus(rs.getString(1));
            ubean.setNamaStatus(rs.getString(2));

            list.add(ubean);

        }
        return list;
    }
  public List getProvinsi() throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from jenis");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            UserBean ubean = new UserBean();
            ubean.setIdJenis(rs.getString(1));
            ubean.setNamaJenis(rs.getString(2));

            list.add(ubean);

        }
        return list;
    }
    public void UpateDetails(UserBean ubean, String name) throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("update userdetails set fullName=?,DateOfBirth=?,email=?,phoneNo=?,password=md5(?),prov_id=?,kab_id=?,kec_id=?,desa_id=? where userName=? ");
        //set values to prepared statement object by getting values from bean object
        pstmt.setString(1, ubean.getfullName());
        pstmt.setString(2, ubean.getDateOfBirth());
        pstmt.setString(3, ubean.getEmail());
        pstmt.setString(4, ubean.getPhoneNo());
        pstmt.setString(5, ubean.getPassword()+"rbplsalting#12345");
        pstmt.setString(6, ubean.getProvinsi());
        pstmt.setString(7, ubean.getKabupaten());
        pstmt.setString(8, ubean.getKecamatan());
        pstmt.setString(9, ubean.getDesa());
        pstmt.setString(10, name);
        pstmt.executeUpdate();

    }

    public void deleteDetails(String uname) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("delete from userdetails where userName=?");
        pstmt.setString(1, uname);
        pstmt.executeUpdate();
    }
    

        public int insertDetails2(UserBean bb) throws SQLException, ClassNotFoundException {
        try {
            Connection con = createConnection();
            PreparedStatement pstmt = con.prepareStatement("insert into senjata (idSenjata, namaSenjata, jenis, idGudang, thnDibuat, thnDibeli, status) values(?,?,?,?,?,?,?)");
            //set values to prepared statement object by getting values from bean object
              pstmt.setString(1, bb.getIdSenjata());
            pstmt.setString(2, bb.getNamaSenjata());
            pstmt.setString(3, bb.getJenis());
            pstmt.setString(4, bb.getIdGudang());
            pstmt.setString(5, bb.getThnDibuat());
            pstmt.setString(6, bb.getThnDibeli());
            pstmt.setString(7, bb.getStatus());
           
            int i = pstmt.executeUpdate();
           
            return i;
        } catch (Exception e) {
            System.out.println(e);
            return 0;
        }
    }

    public List getAlldetails2() throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        
        PreparedStatement pstmt = con.prepareStatement("select senjata.idSenjata, senjata.namaSenjata, jenis.nama as jenis, gudang.nama as idGudang, senjata.thnDibuat, senjata.thnDibeli, statuss.nama as status from statuss,senjata,jenis,gudang where senjata.jenis=jenis.id and senjata.status=statuss.id and senjata.idGudang=gudang.id and senjata.idGudang='"+idGudangAdmin+"'");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            UserBean ubean = new UserBean();
            ubean.setIdSenjata(rs.getString(1));
            ubean.setNamaSenjata(rs.getString(2));
            ubean.setJenis(rs.getString(3));
            ubean.setIdGudang(rs.getString(4));
            ubean.setThnDibuat(rs.getString(5));
            ubean.setThnDibeli(rs.getString(6));
            ubean.setStatus(rs.getString(7));
            list.add(ubean);

        }
        return list;
    }

     public List getAlldetailsok(UserBean bean, String userName) throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
         PreparedStatement pstmt1 = con.prepareStatement("select idGudangAdmin from userdetails where userName='" + userName + "'");
            ResultSet rs1 = pstmt1.executeQuery();
            System.out.println(rs1);
            while (rs1.next()) {

                idGudangAdmin = rs1.getString(1);
                System.out.println("idgud " + idGudangAdmin);

            }

        PreparedStatement pstmt = con.prepareStatement("select senjata.idSenjata, senjata.namaSenjata, jenis.nama as jenis, gudang.nama as idGudang, senjata.thnDibuat, senjata.thnDibeli, statuss.nama as status from statuss,senjata,jenis,gudang where senjata.jenis=jenis.id and senjata.status=statuss.id and senjata.idGudang=gudang.id and senjata.idGudang='"+idGudangAdmin+"'");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            UserBean ubean = new UserBean();
            ubean.setIdSenjata(rs.getString(1));
            ubean.setNamaSenjata(rs.getString(2));
            ubean.setJenis(rs.getString(3));
            ubean.setIdGudang(rs.getString(4));
            ubean.setThnDibuat(rs.getString(5));
            ubean.setThnDibeli(rs.getString(6));
            ubean.setStatus(rs.getString(7));
            list.add(ubean);

        }
        return list;
    }

 public List getResult2(String nilai) throws SQLException, ClassNotFoundException {
    

    Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select DISTINCT idSenjata, namaSenjata, jenis, idGudang, thnDibuat, thnDibeli, status  from senjata where namaSenjata='"+nilai+"'" );
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
                UserBean ubean = new UserBean();
          ubean.setIdSenjata(rs.getString(1));
            ubean.setNamaSenjata(rs.getString(2));
            ubean.setJenis(rs.getString(3));
            ubean.setIdGudang(rs.getString(4));
            ubean.setThnDibuat(rs.getString(5));
            ubean.setThnDibeli(rs.getString(6));
            ubean.setStatus(rs.getString(7));
            list.add(ubean);

    }
        
        return list;
    }
 
 
    public UserBean getDetails2(String idSenjata) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select senjata.idSenjata, senjata.namaSenjata, senjata.jenis, jenis.nama as jenis, senjata.idGudang, gudang.nama as idGudang, senjata.thnDibuat, senjata.thnDibeli, senjata.status, statuss.nama as status from statuss,senjata,jenis,gudang where senjata.jenis=jenis.id and senjata.status=statuss.id and senjata.idGudang=gudang.id and idSenjata=?");
        pstmt.setString(1, idSenjata);
        ResultSet rs = pstmt.executeQuery();

        UserBean ubean = new UserBean();
        while (rs.next()) {

           ubean.setIdSenjata(rs.getString(1));
            ubean.setNamaSenjata(rs.getString(2));
            ubean.setJenis(rs.getString(3));
            ubean.setNamaJenis(rs.getString(4));
            ubean.setIdGudang(rs.getString(5));
            ubean.setNamaGudang(rs.getString(6));
            ubean.setThnDibuat(rs.getString(7));
            ubean.setThnDibeli(rs.getString(8));
            ubean.setStatus(rs.getString(9));
            ubean.setNamaStatus(rs.getString(10));
        }
        ubean.setAction("ubah");
        return ubean;
    }

 
    public void UpdateDetails2(UserBean ubean, String idSenjata) throws SQLException, ClassNotFoundException {
        System.out.println("yoyoyoyoyoyo");
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("update senjata set namaSenjata=?,jenis=?,idGudang=?,thnDibuat=?,thnDibeli=?,status=? where idSenjata=? ");
        //set values to prepared statement object by getting values from bean object
        pstmt.setString(1, ubean.getNamaSenjata());
        pstmt.setString(2, ubean.getJenis());
        pstmt.setString(3, ubean.getIdGudang());
        pstmt.setString(4, ubean.getThnDibuat());
        pstmt.setString(5, ubean.getThnDibeli());
         pstmt.setString(6, ubean.getStatus());
        pstmt.setString(7, idSenjata);
        System.out.println("pstmt"+pstmt);
        pstmt.executeUpdate();

    }

    public void deleteDetails2(String idSenjata) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("delete from senjata where idSenjata=?");
        pstmt.setString(1, idSenjata);
        pstmt.executeUpdate();
    }
    
    public void idGudangs(UserBean ubean, String name) throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select idGudangAdmin from userdetails where userName='"+name+"' ");
        //set values to prepared statement object by getting values from bean object
        pstmt.setString(1, ubean.getfullName());
       pstmt.executeQuery();

    }
  
}
